#coding:utf-8
import openpyxl
# 导入自定义的模块
from model import Student

class ExcelOperate(object):

    def __init__(self, file_name):
        self.__file_name = file_name
        #加载Excel文件
        self.__file = openpyxl.load_workbook(file_name)

    """
    查询对应的学号的密码
    """
    def check_password(self, sheet_name, id, password):
        work_sheet = self.__file[sheet_name]
        # 最大存储数据的行号
        max_row_number = work_sheet.max_row 

        res = False
        for i in range(2, max_row_number+1):
            if str(work_sheet.cell(i, 5).value) == str(id):
                if str(work_sheet.cell(i, 8).value) == str(password):
                    res =  True
                else:
                    res =  False
        
        self.__file.close()
        return res

    '''
    读取表格中所有学生的信息，并返回一个列表
    '''
    def read_sheet(self, sheet_name):
        work_sheet = self.__file[sheet_name]

        # 学生列表
        stuList = []

        # 最大存储数据的行号
        max_row_number = work_sheet.max_row

        # 第1行是表头，因此从第2行开始遍历
        for i in range(2, max_row_number+1):
            name = work_sheet.cell(i, 1).value
            age = work_sheet.cell(i, 2).value
            sex = work_sheet.cell(i, 3).value
            birthPlace = work_sheet.cell(i, 4).value
            school_id = work_sheet.cell(i, 5).value
            college = work_sheet.cell(i, 6).value
            major = work_sheet.cell(i, 7).value
            password = work_sheet.cell(i, 8).value
            new_stu = Student(name, age, sex, birthPlace, school_id, college, major, password)
            stuList.append(new_stu)

        self.__file.close()
        return stuList

    """
    删除指定学号的学生的信息:
    在遍历表格的时候，判断当前行是不是要删除的学生？
    """
    def delete_byID(self, sheet_name, school_id):
        work_sheet = self.__file[sheet_name]

        max_row_number = work_sheet.max_row

        for i in range(2, max_row_number+1):
            # 取出当前行学生的学号
            tmp_id = str(work_sheet.cell(i, 5).value)
            if tmp_id == school_id:
                work_sheet.delete_rows(i)
                break
            else:
                continue
        
        # 删除某一行只是在缓冲区中删除了，如果要持久删除需要持久保存
        self.__file.save(self.__file_name)
        self.__file.close()
    
    """
    更新学生的信息，允许老师修改的信息有：学院、专业
    """
    def update_byId(self, sheet_name, school_id, new_stu):
        work_sheet = self.__file[sheet_name]

        max_row_number = work_sheet.max_row
        max_col_number = work_sheet.max_column

        for i in range(2, max_row_number+1):
            tmp_id = str(work_sheet.cell(i, 5).value)
            if tmp_id == school_id:
                # new_stu是一个字典结构
                work_sheet.cell(i, 6).value = new_stu["学院"]
                work_sheet.cell(i, 7).value = new_stu["专业"]
                work_sheet.cell(i, 8).value = new_stu["密码"]
                break
            else:
                continue
        
        # 保存修改结果
        self.__file.save(self.__file_name)
        self.__file.close()
    
    """
    添加一个新学生的信息
    """
    def add_new_stu(self, sheet_name, new_stu):
        work_sheet = self.__file[sheet_name]
        max_row_number = work_sheet.max_row

        work_sheet.cell(max_row_number+1, 1).value = new_stu.get_name()
        work_sheet.cell(max_row_number+1, 2).value = new_stu.get_age()
        work_sheet.cell(max_row_number+1, 3).value = new_stu.get_sex()
        work_sheet.cell(max_row_number+1, 4).value = new_stu.get_birthPlace()
        work_sheet.cell(max_row_number+1, 5).value = new_stu.get_id()
        work_sheet.cell(max_row_number+1, 6).value = new_stu.get_college()
        work_sheet.cell(max_row_number+1, 7).value = new_stu.get_major()
        work_sheet.cell(max_row_number+1, 8).value = new_stu.get_password()

        self.__file.save(self.__file_name)
        self.__file.close()
    
    """
    根据学号查询学生的信息
    """
    def query_message(self, sheet_name, id):
        work_sheet = self.__file[sheet_name]

        max_row_number = work_sheet.max_row

        for i in range(2, max_row_number+1):
            if str(work_sheet.cell(i, 5).value) == str(id):
                print("id = ", id)
                return { \
                    "name": work_sheet.cell(i, 1).value, \
                    "age": work_sheet.cell(i, 2).value, \
                    "sex": work_sheet.cell(i, 3).value, \
                    "birthPlace": work_sheet.cell(i, 4).value, \
                    "id": work_sheet.cell(i, 5).value, \
                    "college": work_sheet.cell(i, 6).value, \
                    "major": work_sheet.cell(i, 7).value}
                break 
        
        self.__file.close()
        
            
if __name__=='__main__':
    excel_operate = ExcelOperate("stuLists.xlsx")
    new_stu = {
        "姓名": "张三",
        "年龄": 23,
        "性别": "男",
        "籍贯": "上海",
        "学号": "10002",
        "学院": "计算机学院",
        "专业": "电子科学与技术",
        "密码": "1"
    }
    excel_operate.add_new_stu("stuLists", new_stu)


  